package com.xbniao.owms.utils;
import java.lang.reflect.Method;
import java.lang.reflect.Type;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.springframework.util.StringUtils;

import com.xbniao.owms.business.enums.OwmsApiStatusCode;

public class POIExcelCellUtil {
	public static final DecimalFormat format = new DecimalFormat("0");  
	public static String getStringValue(Cell cell) throws Exception{
		 /**
	     * @see Cell#CELL_TYPE_BLANK  空
	     * @see Cell#CELL_TYPE_NUMERIC double
	     * @see Cell#CELL_TYPE_STRING  string
	     * @see Cell#CELL_TYPE_FORMULA 公式
	     * @see Cell#CELL_TYPE_BOOLEAN boolean
	     * @see Cell#CELL_TYPE_ERROR   错误
	     */
		int cellType = cell.getCellType();
		switch (cellType) {
			case Cell.CELL_TYPE_NUMERIC:
				if(HSSFDateUtil.isCellDateFormatted(cell)){
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					return sdf.format(cell.getDateCellValue());
				}else{
					DecimalFormat df = new DecimalFormat("#");
					return df.format(cell.getNumericCellValue());
				}
				
			case Cell.CELL_TYPE_STRING:
				return cell.getStringCellValue();
			case Cell.CELL_TYPE_BLANK:
				return "";
			case Cell.CELL_TYPE_BOOLEAN:
				return String.valueOf(cell.getBooleanCellValue());
			case Cell.CELL_TYPE_ERROR:
				throw new Exception(OwmsApiStatusCode.OWMS_FORMAT_ERROR.getCode());
			default:
				break;
		}
		return "";
	}
	public static String getStringValueByDecimalFormat(Cell cell) 
	{
		return format.format(cell.getNumericCellValue());
	}
	public static String getStringValue(Cell cell,SimpleDateFormat format) throws Exception{
		int cellType = cell.getCellType();
		if(HSSFDateUtil.isCellDateFormatted(cell)){
			return format.format(cell.getDateCellValue());
		}
		switch (cellType) {
			case Cell.CELL_TYPE_NUMERIC:
				return String.valueOf(cell.getNumericCellValue());
			case Cell.CELL_TYPE_STRING:
				return cell.getStringCellValue();
			case Cell.CELL_TYPE_BLANK:
				return "";
			case Cell.CELL_TYPE_BOOLEAN:
				return String.valueOf(cell.getBooleanCellValue());
			case Cell.CELL_TYPE_ERROR:
				throw new Exception(OwmsApiStatusCode.OWMS_FORMAT_ERROR.getCode());
			default:
				break;
		}
		return "";
	}
	public static Short getShortValue(Cell cell)throws Exception{
		 if( cell.getCellType() == Cell.CELL_TYPE_BLANK)
			return null; 
		 if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
			 return (short)cell.getNumericCellValue();
		 else
			 throw  new Exception(OwmsApiStatusCode.OWMS_FORMAT_ERROR.getCode());
	}
	public static Integer getIntValue(Cell cell)throws Exception{
		if( cell.getCellType() == Cell.CELL_TYPE_BLANK)
			return null; 
		if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
			 return (int)cell.getNumericCellValue();
		if(cell.getCellType() == Cell.CELL_TYPE_STRING)
		{
			int returnint = 0;
			try {
				returnint = Integer.parseInt(cell.getStringCellValue());
			} catch (Exception e) {
				e.printStackTrace();
				throw  new Exception(OwmsApiStatusCode.OWMS_FORMAT_ERROR.getCode());
			}
			return returnint;
		}
		 else
			 throw  new Exception(OwmsApiStatusCode.OWMS_FORMAT_ERROR.getCode());
	
	}
	public static double getDoubleValue(Cell cell)throws Exception{
		if( cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK)
			return 0d; 
		if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
			return  cell.getNumericCellValue();
		if(cell.getCellType() == Cell.CELL_TYPE_FORMULA)
			return  cell.getNumericCellValue();
		if(cell.getCellType() == Cell.CELL_TYPE_STRING)
		{
			double returnDouble = 0d;
			try {
				if(cell.getStringCellValue() == null)
					return returnDouble;
				returnDouble = Double.parseDouble(cell.getStringCellValue());
			} catch (Exception e) {
				return 0d;
				//throw  new Exception(OwmsApiStatusCode.OWMS_FORMAT_ERROR.getCode());
			}
			return returnDouble;
		}
		else
			 throw  new Exception(OwmsApiStatusCode.OWMS_FORMAT_ERROR.getCode());
	
	}
	public static Date getDateValue(Cell cell)throws Exception{
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
			if(HSSFDateUtil.isCellDateFormatted(cell)){
				return new Date(cell.getDateCellValue().getTime());
			}
		 }else if(cell.getCellType() == Cell.CELL_TYPE_STRING){
			 String dateStr = cell.getStringCellValue();
			 if(!StringUtils.isEmpty(dateStr) && dateStr.length()==10){
				 return new Date(sdf.parse(dateStr).getTime());
			 }
			 if(!StringUtils.isEmpty(dateStr) && dateStr.length()==16){
				 sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");
				 return  new Date(sdf.parse(dateStr).getTime());
			 }
			 if(!StringUtils.isEmpty(dateStr) && dateStr.length()==19){
				 sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				 return  new Date(sdf.parse(dateStr).getTime());
			 }
			 sdf = new SimpleDateFormat("yyyy-MM-dd");
			 return  new Date(sdf.parse(dateStr.substring(0,10)).getTime());
		 }
		throw new Exception("单元格格式错误"); 
	}
	/**
	 * 根据方法名称取得反射方法的返回类型
	 * @param obj         类实例  
	 * @param methodName  方法名
	 * @return
	 * @throws ClassNotFoundException
	 */
	public static Type getMethodParamTypes(Object obj, String methodName)throws ClassNotFoundException {
		Method[] methods = obj.getClass().getMethods();// 全部方法
		Type type = null;
		for (int i = 0; i < methods.length; i++) {
			if (methodName.equals(methods[i].getName())) {// 和传入方法名匹配
				type = methods[i].getGenericReturnType();
				break;
			}
		}
		return type;
	}
	public static String FORMAT_DOUBLE(Object d){
		DecimalFormat df = new DecimalFormat("#.00");
		df.applyPattern("0.00");
		return df.format(d);
	}
	public static void main(String[] args) {
		System.out.println(new Double(Integer.MAX_VALUE).toString());
		System.out.println(POIExcelCellUtil.FORMAT_DOUBLE(0.001));
	}
}
